Database load reducing system and load reducing program

ABSTRACT

An object of the present invention is to provide a database load alleviation system and load alleviation program which balance load by interpreting queries and distributing queries which enable load balancing to database servers. In a database load alleviation system, a query-interpretive load balancer  1  interprets queries received from a user terminal  10 , and judges whether the queries enable load balancing. Queries enabling load balancing are each distributed in sequence to one among the database servers  2 - 1  to  2 - n  in accordance with a load balancing algorithm, to balance the load. On the other hand, in the case of queries which do not enable load balancing such as a query which performs data writing, the query is replicated, and replicas are transmitted to all database servers  2 - 1  to  2 - n , to maintain database consistency.

TECHNICAL FIELD

The present invention relates to a database load alleviation system, andin particular is related to a database load alleviation system and loadalleviation program which alleviate the load by interpreting queries andsequentially distributing the queries to a plurality of databaseservers.

BACKGROUND ART

With the spread of the Internet in recent years, there has been a rapidincrease in the number of persons using databases over computernetworks. When numerous queries from users are sent to databases, theload on the server providing the database increases, so that performanceof the server declines, and the processing time from the time a query isissued until the query results are obtained becomes longer. Inparticular, when synchronous access is used as access to a database, thetime until acquisition of a query result and advance to the nextprocessing is lengthened, and operability is degraded. Hence technologyis needed to alleviate the load on the database server and efficientlyprocess the queries from numerous users.

Conventionally, one method for alleviating loads on database serversentails installation of a plurality of database servers, anddistributing queries to any one among the plurality of database servers,to suppress the loads on each database server (see for exampleNon-patent document 1). In order to synchronize and maintain consistencyamong contents of a plurality of databases when using this method, afile server may be used; each of the database servers shares a databaseinstalled in the file server, so that consistency of the contents can bemaintained.

In the above-described conventional method of distributing queries, whenqueries for adding new data or queries for performing update procedureare distributed to only one server among the plurality of databaseservers, the contents of the databases provided by the differentdatabase servers become different, and so there is the problem thatconsistency among the databases cannot be maintained. Further, whenusing a file server in order to maintain consistency, the load isconcentrated on the file server which causes the databases to havecommon contents, and so the performance of the system providing thedatabase declines. In order to avoid such a decline in performance,there is the problem that a huge amount of resources must be providedfor the file server.

DISCLOSURE OF THE INVENTION

An object of the present invention is to provide a database loadalleviation system and load alleviation program which interprets queriesfrom users to database servers, divides the queries into types, performseffective load balancing by distribution to a plurality of databaseservers in the case of queries which enable load balancing, and in thecase of queries such as for data writing which are not suited to performload balancing, executes the queries on all database servers to maintainconsistency among the databases.

The gist of the present invention is a database load alleviation system,comprising a plurality of database servers; and a load alleviationdevice which manages the plurality of database servers, wherein the loadalleviation device comprises: a query interpretation unit whichinterprets received queries, and judges whether the queries enable loadbalancing of the database servers; a query distribution unit whichsequentially distributes and transmits to the database servers, thosequeries judged by the query interpretation unit to enable loadbalancing; a replication unit which replicates those queries judged bythe query interpretation unit not to enable load balancing; and a querytransmission unit which transmits to all managed database servers,queries replicated by the replication unit.

The above configuration is appropriate for realization of balancing ofthe load on database servers and maintenance of the consistency of aplurality of database servers.

Further, the gist of the present invention is the above-describeddatabase load alleviation system, in which the query interpretation unitjudges whether the received queries match a regular expression whichreads data, and in the case of the regular expression which performsreading, judges the queries to be queries which enable load balancing.

Further, the gist of the present invention is the above-describeddatabase load alleviation system, in which the query distribution unituses a predetermined load alleviation algorithm to select one serveramong the plurality of database servers, in order and in loopingfashion, for distribution of a query judged by the query-interpretationunit to enable load balancing.

Further, the gist of the present invention is the above-describeddatabase load alleviation system, in which the query execution unittransmits the queries replicated by the replication unit to all thedatabase servers in sequence, starting from a database server to which anext received query which enables load balancing is to be distributed,following a previously distributed query.

As explained above, according to the present invention, queries areinterpreted, and in the case of queries enabling load balancing, thequeries are distributed to a plurality of database servers in sequencein accordance with a load-balancing algorithm such as round-robin methodto balance the load; whereas in the case of queries requiring processingto write to a database, the queries are transmitted to and executed byall database servers, so that database consistency is maintained whilebalancing the load. Hence declines in the performance of databaseservers due to increases in the load can be prevented, and there is thefurther advantage that database reliability can be preserved.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a conceptual diagram showing an embodiment of the presentinvention; and,

FIG. 2 is a conceptual diagram showing the processing of an “update”statement or similar.

BEST MODE FOR CARRYING OUT THE INVENTION

Below, an embodiment of the present invention is explained referring tothe drawings. FIG. 1 is a conceptual diagram showing the embodiment ofthe present invention. In the figure, symbols 2-1 to 2-n (where n is apositive integer) are database servers providing a database. The numberof database servers 2-1 to 2-n is determined based on the required loadalleviation rate, calculated taking the number of queries intoconsideration. The symbol 1 denotes a load alleviation device (hereaftercalled a query-interpretive load balancer) which interprets queriesreceived from a user terminal 10, and distributes and transmits eachquery to one among the database servers 2-1 to 2-n, or transmits thequery to all the database servers 2-1 to 2-n.

The query-interpretive load balancer 1 interprets a query from the userterminal 10, and judges whether the query enables load balancing. Here,a query addressed to a database server is assumed to be written in SQL(Structured Query Language), which is the most widespread programminglanguage for database manipulation. The query-interpretive load balancer1 focuses on the content of the SQL code to make a judgment. Forexample, a database reference-type query represented by a “select”statement, etc., performs database reading, and so is judged to be aquery enabling load balancing.

The following is an example of a regular expression of a Postgre SQL“select” statement.

ˆQ[\tΔ]*select[\tΔ]+.*;$

In Postgre SQL, The leading “ˆ” of the SQL statement is followed by thequery-identifying character “Q”, to declare that the statement is aquery. As in the above example, after the “Q” the “select” statementfollows. A space or TAB may be inserted between the “Q” and the “select”statement. In order to judge that the query enables load balancing, thisregular expression is used to decide whether the query is areference-type query which only references the database, as expressed bya “select” statement or similar.

Queries sent to the above-described query-interpretive load balancer 1are not queries with a special structure; queries intended for ordinarydatabase servers can be used.

Other types of queries which do not match this regular expression, forexample, update-type queries expressed using “update”, “insert”,“delete”, or other statements which change data in the database, performwriting to the database, and so are interpreted as queries which cannotbe used in load balancing. An example of Postgre SQL is shown, but thereare no limitations on the language of use, and other languages may beused.

The query-interpretive load balancer 1 employs a predetermined existingload balancing algorithm (a round-robin method, weighted priority ordermethod, number-of-connections method, response time method, HTTP (HyperText Transfer Protocol) header method, or an algorithm which combinesthese, or similar), such as a round-robin method, to distribute queriesjudged to enable load balancing to the database servers 2-1 to 2-n. Inround-robin distribution, one among the database servers 2-1 to 2-n isselected in ascending order, and in looping fashion, for querydistribution. In the case of a query not enabling load balancing, thequery is replicated to generate n queries (the number of databaseservers), and these queries are transmitted for execution to all thedatabase servers 2-1 to 2-n. The queries are transmitted to all thedatabase servers 2-1 to 2-n in sequence, beginning from the databaseserver to which a next received query enabling load balancing is to bedistributed, following the query previously distributed in accordancewith a round-robin method. The query-interpretive load balancer 1 alsotransmits one confirmation data item, among the similar confirmationdata items indicating the end of processing in response to the queriestransmitted from each of the database servers 2-1 to 2-n, such as forexample the confirmation data item received last from the databaseservers 2-1 to 2-n, to the user terminal 10.

Next, operation of the embodiment of the present invention is explainedreferring to FIG. 1. First, a query input from the user terminal 10 issent via a communication network to the query-interpretive load balancer1. The query-interpretive load balancer 1 interprets the query, andjudges whether the query matches the regular expression of a “select”statement or other reference-type query. If the query does so match, thequery is judged to be a query enabling load balancing, and is sent inaccordance with a round-robin method. In round-robin distribution, oneamong the database servers 2-1 to 2-n is selected sequentially inascending order, and queries are distributed and transmitted.

Upon receiving a query, a database server, such as for example databaseserver 2-i, executes the query and returns the query result to the userterminal 10 through the interpretive load balancer 1. If aftertransmitting the query to the database server the query-interpretiveload balancer 1 does not acquire a query result, despite thepredetermined time having elapsed, the query is distributed to the nextdatabase server in order.

The load balancing effect of distribution of queries comprising “select”statements to a plurality of database servers as described above isevaluated by measuring the load on the CPU (Central Processing Unit) ofthe database servers; according to these results, when all queries arereference types employing “select” statements or similar, when there aretwo database servers (n=2), it is found that the CPU utilization rate isimproved by an average of approximately 60%.

On the other hand, when a query does not match a regular expression of a“select” statement, and is judged to be a query not enabling loadbalancing, the query is replicated to generate n queries. FIG. 2 showsprocessing for the case in which the query is an “update” statement. Oneof the replicated queries is first transmitted to the database server towhich the query enabling load balancing to be received next would havebeen distributed, following the query previously distributed inaccordance with a round-robin method. Then, the queries are transmittedto all the database servers 2-1 to 2-n in sequence. If there is noresponse to the query even when a predetermined time has elapsed aftertransmission of the query, execution advances to the next databaseserver.

1. A database load alleviation system comprising: a plurality ofdatabase servers; and a load alleviation device which manages theplurality of database servers, wherein the load alleviation devicecomprises: a query interpretation unit which interprets receivedqueries, and judges whether the queries enable load balancing of thedatabase servers; a query distribution unit which sequentiallydistributes and transmits to the database servers, those queries judgedby the query interpretation unit to enable load balancing; a replicationunit which replicates those queries judged by the query interpretationunit not to enable load balancing; and a query transmission unit whichtransmits to all managed database servers, queries replicated by thereplication unit.
 2. The database load alleviation system according toclaim 1, wherein the query interpretation unit judges whether thereceived queries match a regular expression which reads data, and in thecase of the regular expression which performs reading, judges thequeries to be queries which enable load balancing.
 3. The database loadalleviation system according to claim 1, wherein the query distributionunit uses a predetermined load alleviation algorithm to select oneserver among the plurality of database servers, in order and in loopingfashion, for distribution of a query judged by the query interpretationunit to enable load balancing.
 4. The database load alleviation systemaccording to claim 1, wherein the query transmission unit transmits thequeries replicated by the replication unit to all the database serversin sequence, starting from a database server to which a next receivedquery which enables load balancing is to be distributed, following apreviously distributed query.
 5. A load alleviation program, in adatabase load alleviation system comprising a plurality of databaseservers and a load alleviation device which manages the plurality ofdatabase servers, causing a computer to execute: a function ofinterpreting received queries and judging whether the queries enableload balancing of the database servers; a function of distributing andtransmitting those queries judged to enable load balancing in sequenceto the database servers in accordance with a load balancing algorithm; afunction of replicating those queries judged not to enable loadbalancing; and a function of transmitting replicated queries to allmanaged database servers.